﻿
using System.Data.SqlClient;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Web;
using System.Text;
using System.IO;
using System.Configuration;
/// <summary>
/// Summary description for DBUtil
/// </summary>
namespace Model
{

    public class DBUtil
    {
        public string adminOrUser = "1";
        public string MemberUser{ get; set; }
        public string strConn { get; set; }

        public DBUtil()
        {
        }


        public DataTable ExecuteReader(string strCommand)
        {
            //
            //connect database
            //
            SqlConnection myConn = new SqlConnection();
            myConn.ConnectionString = strConn;

            //check if connection is open
            if (myConn.State == ConnectionState.Open) myConn.Close();

            DataTable dt = new DataTable();
            myConn.Open();


            SqlDataAdapter myAdapter = new SqlDataAdapter("", myConn);
            myAdapter.SelectCommand.CommandType = CommandType.Text;
            myAdapter.SelectCommand.CommandText = strCommand;

            try
            {
                myAdapter.SelectCommand.ExecuteReader();
            }
            catch
            {
                myConn.Close();
                return null;
            }
            myConn.Close();

            myAdapter.Fill(dt);

            return dt;
        }

        public DataTable ExecuteReader(string strCommand, SqlParameter[] prmArray)
        {
            SqlConnection myConn = new SqlConnection();
            myConn.ConnectionString = strConn;

            if (myConn.State == ConnectionState.Open)
                myConn.Close();

            DataTable dt = new DataTable();
            myConn.Open();

            SqlDataAdapter myAdapter = new SqlDataAdapter("", myConn);
            myAdapter.SelectCommand.CommandType = CommandType.Text;
            myAdapter.SelectCommand.CommandText = strCommand;

            if (prmArray != null)
            {

                foreach (SqlParameter prm in prmArray)
                {
                    myAdapter.SelectCommand.Parameters.Add(prm);
                }
            }
            try
            {
                myAdapter.SelectCommand.ExecuteReader();
            }
            catch
            {
                myConn.Close();
                return null;
            }
            myConn.Close();
            myAdapter.Fill(dt);

            return dt;

        }

        public string ExecuteScalar(string strCommand)
        {
            SqlConnection connSql = new SqlConnection();
            connSql.ConnectionString = strConn;
            if (connSql.State == ConnectionState.Open)
                connSql.Close();

            connSql.Open();

            SqlCommand Sqlcomm = new SqlCommand(strCommand, connSql);
            Sqlcomm.CommandType = CommandType.Text;

            string ResultValue = "";
            try
            {
                ResultValue = Sqlcomm.ExecuteScalar().ToString();
            }
            catch
            {
                connSql.Close();
                return "";
            }
            connSql.Close();

            return ResultValue;
        }

        public string ExecuteScalar(string strCommand, SqlParameter[] prmArray)
        {
            SqlConnection connSql = new SqlConnection();
            connSql.ConnectionString = strConn;
            if (connSql.State == ConnectionState.Open)
                connSql.Close();

            connSql.Open();

            SqlCommand Sqlcomm = new SqlCommand(strCommand, connSql);
            Sqlcomm.CommandType = CommandType.Text;

            if (prmArray != null)
            {
                foreach (SqlParameter prm in prmArray)
                {
                    Sqlcomm.Parameters.Add(prm);
                }
            }
            string ResultValue = "";
            try
            {
                ResultValue = Sqlcomm.ExecuteScalar().ToString();
            }
            catch
            {
                connSql.Close();
                return "";
            }
            connSql.Close();

            return ResultValue;
        }
        public string ExecuteScalarProc(string strProcedureName, SqlParameter[] prmArray)
        {
            SqlConnection connSql = new SqlConnection();
            connSql.ConnectionString = strConn;
            if (connSql.State == ConnectionState.Open)
                connSql.Close();
            try
            {
                connSql.Open();
            }
            catch
            {
                return "";
            }
            SqlCommand Sqlcomm = new SqlCommand(strProcedureName, connSql);
            Sqlcomm.CommandType = CommandType.StoredProcedure;

            if (prmArray != null)
            {
                foreach (SqlParameter prm in prmArray)
                {
                    Sqlcomm.Parameters.Add(prm);
                }
            }
            string ResultValue = "";
            try
            {
                ResultValue = Sqlcomm.ExecuteScalar().ToString();
            }
            catch
            {
                connSql.Close();
                return "";
            }
            connSql.Close();

            return ResultValue;
        }

        public int ExecuteNonQuery(string strCommand)
        {
            SqlConnection connSql = new SqlConnection();
            connSql.ConnectionString = strConn;
            if (connSql.State == ConnectionState.Open)
                connSql.Close();

            connSql.Open();
            SqlTransaction trans = connSql.BeginTransaction();
            SqlCommand Sqlcomm = new SqlCommand(strCommand, connSql);
            Sqlcomm.CommandType = CommandType.Text;
            Sqlcomm.Transaction = trans;

            try
            {
                Sqlcomm.ExecuteNonQuery();
            }
            catch
            {
                trans.Rollback();
                connSql.Close();
                return -1;
            }
            trans.Commit();
            connSql.Close();
            return 0;
        }

        public int ExecuteNonQuery(string strCommand, SqlParameter[] prmArray)
        {
            SqlConnection connSql = new SqlConnection();
            connSql.ConnectionString = strConn;
            if (connSql.State == ConnectionState.Open)
                connSql.Close();

            connSql.Open();
            SqlTransaction trans = connSql.BeginTransaction();
            SqlCommand Sqlcomm = new SqlCommand(strCommand, connSql);
            Sqlcomm.CommandType = CommandType.Text;
            Sqlcomm.Transaction = trans;

            if (prmArray != null)
            {
                foreach (SqlParameter prm in prmArray)
                {
                    Sqlcomm.Parameters.Add(prm);
                }
            }

            try
            {
                Sqlcomm.ExecuteNonQuery();
            }
            catch
            {
                trans.Rollback();
                connSql.Close();
                return -1;
            }
            trans.Commit();
            connSql.Close();
            return 0;


        }

        public DataTable ExecuteReaderProc(string strProcedureName, SqlParameter[] prmArray)
        {
            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlDataAdapter sqlAdapt = new SqlDataAdapter(strProcedureName, sqlConn);
            sqlAdapt.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (prmArray != null)
            {
                foreach (SqlParameter param in prmArray)
                {
                    sqlAdapt.SelectCommand.Parameters.Add(param);
                }
            }
            DataTable dtOutput = new DataTable();
            sqlAdapt.Fill(dtOutput);
            //try
            //{
            //    sqlAdapt.Fill(dtOutput);
            //}
            //catch
            //{
            //}
            //finally
            //{
            //}
            return dtOutput;
        }
       
        public DataTable ExecuteReaderProc(string strProcedureName)
        {

            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlDataAdapter sqlAdapt = new SqlDataAdapter(strProcedureName, sqlConn);
            sqlAdapt.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dtOutput = new DataTable();
            try
            {
                sqlAdapt.Fill(dtOutput);
            }
            catch
            {
                return null;
            }
            finally
            {
            }
            return dtOutput;
        }
   
        public int ExecuteNonQueryProc(string strProcedureName, SqlParameter[] prmArray,bool UseOffline)
        {

            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlCommand sqlCmd = new SqlCommand("", sqlConn);
            sqlCmd.CommandText = strProcedureName;
            sqlCmd.CommandType = CommandType.StoredProcedure;

            if (prmArray != null)
            {
                foreach (SqlParameter param in prmArray)
                {
                    sqlCmd.Parameters.Add(param);
                }
            }
            try
            {
                sqlConn.Open();
            }
            catch
            {

            }

            try
            {
                sqlCmd.ExecuteNonQuery();
            }
            catch 
            {
            }
            finally
            {
                sqlConn.Close();
            }
            return 0;
        }

        public int ExecuteNonQueryProc(string strProcedureName,bool UseOffline)
        {
            SqlConnection sqlConn = new SqlConnection(strConn);
            try
            {
                sqlConn.Open();
            }
            catch
            {
                if (UseOffline == true)
                {
                }
                return 1;
            }
            SqlCommand sqlCmd = new SqlCommand("", sqlConn);
            sqlCmd.CommandText = strProcedureName;
            sqlCmd.CommandType = CommandType.Text;

            try
            {
                sqlCmd.ExecuteNonQuery();
            }
            catch
            {

            }
            finally
            {
                sqlConn.Close();
            }
            return 0;
        }
        public DataTable ExecuteReaderProcForDDL(string strProcedureName)
        {
            DataTable dtOutput = new DataTable();

            SqlConnection sqlConn = new SqlConnection(strConn);
            SqlDataAdapter sqlAdapt = new SqlDataAdapter(strProcedureName, sqlConn);
            sqlAdapt.SelectCommand.CommandType = CommandType.StoredProcedure;

            try
            {
                sqlAdapt.Fill(dtOutput);
            }
            catch
            {
                  return dtOutput;
                //return null;
            }
            finally
            {
            }
            return dtOutput;
        }

       }
}